package com.music.mapper.music;

import com.alibaba.fastjson.JSONObject;
import com.music.dto.music.PlaylistDTO;
import com.music.dto.music.SongDTO;
import com.music.entity.music.PlaylistEntity;
import com.music.entity.music.SongEntity;
import org.apache.ibatis.annotations.*;

import java.util.List;

public interface SongMapper {
    /*
     * @Description 根据歌单id查歌
     * @Author DY
     * @Date 2021/11/26
     **/
    @Select({"<script>SELECT song_id as 'key', song_id, song_name, singer_name, album_name, song_info, " +
            "audio_url, audio_name, audio_size, is_public, gmt_create FROM music_song WHERE playlist_id = #{playlistId} " +
            "<if test='songName != null'> AND song_name LIKE CONCAT('%', #{songName}, '%') </if>" +
            "<if test='singerName != null'> AND singer_name LIKE CONCAT('%', #{singerName}, '%') </if>" +
            "<if test='albumName != null'> AND album_name LIKE CONCAT('%', #{albumName}, '%') </if>" +
            "<if test='isPublic != null'> AND is_public = #{isPublic} </if>" +
            "ORDER BY " +
            "<if test='sortField != null'>" +
            "${sortField} ${sortOrder}, " +
            "</if>" +
            "song_id ASC " +
            "LIMIT #{start}, #{length}" +
            "</script>"})
    List<SongDTO> queryByPlaylistId(
            @Param("start") Integer start,
            @Param("length") Integer length,
            @Param("playlistId") Long playlistId,
            @Param("songName") String songName,
            @Param("singerName") String singerName,
            @Param("albumName") String albumName,
            @Param("isPublic") String isPublic,
            @Param("sortField") String sortField,
            @Param("sortOrder") String sortOrder) throws Exception;

    /*
     * @Description 根据歌单id查歌
     * @Author DY
     * @Date 2021/11/26
     **/
    @Select({"<script>SELECT COUNT(*) FROM music_song WHERE playlist_id = #{playlistId} " +
            "<if test='songName != null'> AND song_name LIKE CONCAT('%', #{songName}, '%') </if>" +
            "<if test='singerName != null'> AND singer_name LIKE CONCAT('%', #{singerName}, '%') </if>" +
            "<if test='albumName != null'> AND album_name LIKE CONCAT('%', #{albumName}, '%') </if>" +
            "<if test='isPublic != null'> AND is_public = #{isPublic} </if>" +
            "</script>"})
    Long countByPlaylistId(@Param("playlistId") Long playlistId,
                           @Param("songName") String songName,
                           @Param("singerName") String singerName,
                           @Param("albumName") String albumName,
                           @Param("isPublic") String isPublic) throws Exception;

    /*
     * @Description 新增歌曲
     * @Author DY
     * @Date 2021/11/26
     **/
    @Insert("Insert into music_song ( " +
            "playlist_id, song_name, singer_name, album_name, song_info, " +
            "audio_url, audio_name, audio_size, is_public, gmt_create) " +
            "VALUES (#{songEntity.playlistId}, #{songEntity.songName}, #{songEntity.singerName}, " +
            "#{songEntity.albumName}, #{songEntity.songInfo}, #{songEntity.audioUrl}, " +
            "#{songEntity.audioName}, #{songEntity.audioSize}, #{songEntity.isPublic}, #{songEntity.gmtCreate})")
    Integer addSong(@Param("songEntity") SongEntity songEntity) throws Exception;

    /*
     * @Description 更新歌曲
     * @Author DY
     * @Date 2021/11/26
     **/
    @Update("Update music_song set song_name = #{songEntity.songName}, " +
            "singer_name = #{songEntity.singerName}, album_name = #{songEntity.albumName}, " +
            "song_info = #{songEntity.songInfo}, audio_url = #{songEntity.audioUrl}, " +
            "audio_name = #{songEntity.audioName}, audio_size = #{songEntity.audioSize}, is_public = #{songEntity.isPublic} " +
            "WHERE song_id = #{songEntity.songId}")
    Integer updateSong(@Param("songEntity") SongEntity songEntity) throws Exception;

    /*
     * @Description 删除歌曲
     * @Author DY
     * @Date 2021/11/26
     **/
    @Delete("DELETE from music_song WHERE song_id = #{songId}")
    Integer removeSong(@Param("songId") String songId) throws Exception;

    /*
     * @Description 音乐广场根据歌单id查歌
     * @Author hbh
     * @Date 2021/11/27
     **/
    @Select({"<script>SELECT a.song_id as 'key', a.song_id, b.user_id, song_name, singer_name, album_name, song_info, " +
            "audio_url, audio_name, audio_size, a.gmt_create, user_name contributor, " +
            "(SELECT COUNT(*) count FROM music_download d LEFT JOIN music_song e ON e.song_id = d.song_id WHERE a.song_id = e.song_id) downloadsNumber, " +
            "(SELECT COUNT(*) FROM collection_song WHERE user_id = #{userId} AND song_id = a.song_id) isCollect " +
            "FROM music_song a LEFT JOIN music_playlist b ON a.playlist_id = b.playlist_id " +
            "LEFT JOIN basic_user c on b.user_id = c.user_id " +
            "WHERE a.is_public = 1 and b.is_public = 1 " +
            "<choose> " +
            "<when test='playlistId != null'> " +
            "AND b.playlist_id = #{playlistId} " +
            "<if test='songName != null'> AND song_name LIKE CONCAT('%', #{songName}, '%') </if>" +
            "<if test='singerName != null'> AND singer_name LIKE CONCAT('%', #{singerName}, '%') </if>" +
            "<if test='albumName != null'> AND album_name LIKE CONCAT('%', #{albumName}, '%') </if>" +
            "</when> " +
            "<when test='playlistId == null'> " +
            "AND (song_name = #{songName} or singer_name = #{songName} or album_name = #{songName}) " +
            "AND b.user_id != #{userId} " +
            "</when> " +
            "</choose> " +
            "ORDER BY " +
            "<if test='sortField != null'>" +
            "${sortField} ${sortOrder}, " +
            "</if>" +
            "a.song_id ASC " +
            "LIMIT #{start}, #{length}" +
            "</script>"})
    List<SongDTO> queryDetailByPlaylistId(
            @Param("start") Integer start,
            @Param("length") Integer length,
            @Param("userId") Long userId,
            @Param("playlistId") String playlistId,
            @Param("songName") String songName,
            @Param("singerName") String singerName,
            @Param("albumName") String albumName,
            @Param("sortField") String sortField,
            @Param("sortOrder") String sortOrder) throws Exception;

    /*
     * @Description 音乐广场根据歌单id查歌
     * @Author hbh
     * @Date 2021/11/27
     **/
    @Select({"<script>SELECT COUNT(*) FROM music_song a " +
            "LEFT JOIN music_playlist b ON a.playlist_id = b.playlist_id " +
            "WHERE a.is_public = 1 and b.is_public = 1 " +
            "<choose> " +
            "<when test='playlistId != null'> " +
            "AND b.playlist_id = #{playlistId} " +
            "<if test='songName != null'> AND song_name LIKE CONCAT('%', #{songName}, '%') </if>" +
            "<if test='singerName != null'> AND singer_name LIKE CONCAT('%', #{singerName}, '%') </if>" +
            "<if test='albumName != null'> AND album_name LIKE CONCAT('%', #{albumName}, '%') </if>" +
            "</when> " +
            "<when test='playlistId == null'> " +
            "AND (song_name = #{songName} or singer_name = #{songName} or album_name = #{songName}) " +
            "AND b.user_id != #{userId} " +
            "</when> " +
            "</choose> " +
            "</script>"})
    Long countDetailByPlaylistId(@Param("userId") Long userId,
                                 @Param("playlistId") String playlistId,
                                 @Param("songName") String songName,
                                 @Param("singerName") String singerName,
                                 @Param("albumName") String albumName) throws Exception;


    @Select({"SELECT a.song_id, COUNT(*) count FROM music_download a LEFT JOIN music_song b ON a.song_id = b.song_id " +
            "GROUP BY a.song_id"})
    List<JSONObject> getDownloadNumber() throws Exception;

    /*
     * @Description 根据song_id批量获取音频地址
     * @Author DY
     * @Date 2021/11/29
     **/
    @Select("SELECT song_id, audio_url from music_song where song_id in (${songIds})")
    List<SongDTO> getAudioUrl(@Param("songIds") String songIds) throws Exception;
}
